package repository.impl;

import Util.JDBCtool;
import com.mysql.cj.jdbc.JdbcConnection;
import entity.Book;
import entity.Borrow;
import entity.Reader;
import repository.BorrowRepository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowRepositoryImlp implements BorrowRepository {
    @Override
    public void insert(Integer bookid, Integer readerid, String borrowtime, String returntime, Integer adminid, Integer statement) throws SQLException {
        Connection connection= JDBCtool.getConnection();
        String sql="insert into bookadmin(bookid,reader,borrowtime,returntime,statement) values (?,?,?,?,0)";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setInt(1,bookid);
        preparedStatement.setInt(2,readerid);
        preparedStatement.setString(3,borrowtime);
        preparedStatement.setString(4,returntime);
        preparedStatement.executeUpdate();
        /*JDBCtool.release(connection,preparedStatement,null);*/
    }

    @Override
    public List<Borrow> findAllByReaderId(Integer id,Integer index,Integer limit) throws SQLException {
        Connection connection=JDBCtool.getConnection();
        String sql="select br.id,b.name,b.author,b.publish,br.borrowtime,br.returntime,r.name,r.tel,r.cardid,br.statement " +
                "from bookadmin br,book b,reader r where reader=? and b.id=br.bookid and r.id=br.reader limit ?,?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setInt(2,index);
        statement.setInt(3,limit);
        ResultSet resultSet=statement.executeQuery();
        List<Borrow> list=new ArrayList<>();
        while(resultSet.next()){
            /*resultSet.getString(6) author=resultSet.getString(3);
            String publish=resultSet.getString(4);
            String borrowTime=resultSet.getString(5);
            String returnTime=resultSet.getString(6);
            String readerName=resultSet.getString(7);
            String tel=resultSet.getString(8);
            String cardId=resultSet.getString(9);
            //封装
            Book book=new Book(bookName,author,publish);
            Reader reader=new Reader(readerName,tel,cardId);
            Borrow borrow=new Borrow(borrowId,book,reader,borrowTime,returnTime);
            list.add(borrow);*/
            list.add(new Borrow(resultSet.getInt(1),
                    new Book(resultSet.getString(2),resultSet.getString(3),resultSet.getString(4)),
                    new Reader(resultSet.getString(7),resultSet.getString(8),resultSet.getString(9)),
                    resultSet.getString(5),resultSet.getString(6),resultSet.getInt(10)
                    ));
        }
        JDBCtool.release(connection,statement,resultSet);
        return list;
    }

    @Override
    public int count(Integer readerId) throws SQLException {
        Connection connection=JDBCtool.getConnection();
        String sql="select count(*) " +
                "from bookadmin br,book b,reader r where reader=? and b.id=br.bookid and r.id=br.reader ";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,readerId);
        ResultSet resultSet = statement.executeQuery();
        int count=0;
        if(resultSet.next()){
            count=resultSet.getInt(1);
        }
        JDBCtool.release(connection,statement,resultSet);
        return count;
    }

    @Override
    public List<Borrow> findAllByState(Integer state,Integer index,Integer limit) throws SQLException {
        Connection connection=JDBCtool.getConnection();
        String sql="select br.id,b.name,b.author,b.publish,br.borrowtime,br.returntime,r.name,r.tel,r.cardid,br.statement " +
                "from bookadmin br,book b,reader r where statement=? and b.id=br.bookid and r.id=br.reader limit ?,?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,state);
        statement.setInt(2,index);
        statement.setInt(3,limit);
        ResultSet resultSet=statement.executeQuery();
        List<Borrow> list=new ArrayList<>();
        while(resultSet.next()){
            list.add(new Borrow(resultSet.getInt(1),
                    new Book(resultSet.getString(2),resultSet.getString(3),resultSet.getString(4)),
                    new Reader(resultSet.getString(7),resultSet.getString(8),resultSet.getString(9)),
                    resultSet.getString(5),resultSet.getString(6),resultSet.getInt(10)
            ));
        }
        JDBCtool.release(connection,statement,resultSet);
        return list;
    }

    @Override
    public int countByState(Integer state) throws SQLException {
        Connection connection=JDBCtool.getConnection();
        String sql="select count(*) " +
                "from bookadmin br,book b,reader r where statement=? and b.id=br.bookid and r.id=br.reader ";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,state);
        ResultSet resultSet = statement.executeQuery();
        int count=0;
        if(resultSet.next()){
            count=resultSet.getInt(1);
        }
        JDBCtool.release(connection,statement,resultSet);
        return count;
    }

    @Override
    public void handle(Integer borrowId, Integer state, Integer adminId) throws SQLException {
        Connection connection= JDBCtool.getConnection();
        String sql="update bookadmin set statement =?,adminid=? where id = ?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,state);
        statement.setInt(2,adminId);
        statement.setInt(3,borrowId);
        statement.executeUpdate();
        JDBCtool.release(connection,statement,null);
    }
}
